use bgt_employer_firm_07_to_14.dta, clear

keep if inlist(year, 2007, 2010, 2012)

gen educ=edu
replace educ=maxedu if educ==.

gen edureq=(educ!=.)
	
	gen eduhs=(educ==12 & !missing(educ))
	gen edusc=((educ==13 | educ==14) &!missing(educ))
	gen edubd=(educ==16  &!missing(educ))
	gen eduma=(educ==18 &!missing(educ))
	gen eduphd=(educ==21 &!missing(educ))

gen edubdgd=.
replace edubdgd=1 if (edubd==1 | eduma==1 | eduphd==1)
replace edubdgd=0 if (edubd==0 & eduma==0 & eduphd==0)


gen exper=exp
replace exper=maxexp if exp==.

drop edu maxedu exp maxexp

gen expreq=exper!=.
	
	gen exp_0to1=(exper>0 & exper<1 & !missing(exper))
	gen exp_1to2=(exper>=1 & exper<2 &  !missing(exper))
	gen exp_2to3=(exper>=2 & exper<3 & !missing(exper))
	gen exp_3to4=(exper>=3 & exper<4 & !missing(exper))
	gen exp_4to5=(exper>=4 & exper<5 & !missing(exper))
	gen exp_5to7=(exper>=5 & exper<7 & !missing(exper))
	gen exp_7to15=(exper>=7 & exper<15 & !missing(exper))


gen exp2=.
replace exp2=1 if (exp_2to3==1 | exp_3to4==1 | exp_4to5==1 | exp_5to7==1 | exp_7to15==1)
replace exp2=0 if (exp_2to3==0 & exp_3to4==0 & exp_4to5==0 & exp_5to7==0 & exp_7to15==0)

gen exp4=.
replace exp4=1 if (exp_4to5==1 | exp_5to7==1 | exp_7to15==1)
replace exp4=0 if (exp_4to5==0 & exp_5to7==0 & exp_7to15==0)

drop if fips=="na"
count

assert length(fips)==5

drop if inlist(statefip, 11, 72, 66)
drop if employerid==.
drop if jobid==.

egen empjobid =group(jobid employerid)
replace empjobid=. if employerid==.
replace empjobid=. if jobid==.
egen empjobstateid=group(employerid jobid statefip)

gen occsoc_broad=substr(occsoc,1,2)
gen occsoc_minor=substr(occsoc,1,3)
gen occsoc_detailed=occsoc

destring occsoc_broad, gen(occsoc_broad2)
destring occsoc_minor, gen(occsoc_minor2)
destring occsoc_detailed, gen(occsoc_detailed2)

gen occsoc_broadgroup=2 if occsoc_broad2>=11 & occsoc_broad2<=13
replace occsoc_broadgroup=3 if occsoc_broad2>=15 & occsoc_broad2<=29
replace occsoc_broadgroup=4 if occsoc_broad2>=31 & occsoc_broad2<=39
replace occsoc_broadgroup=5 if occsoc_broad2>=41 & occsoc_broad2<=43
replace occsoc_broadgroup=6 if occsoc_broad2>=45 & occsoc_broad2<=49
replace occsoc_broadgroup=7 if occsoc_broad2>=51 & occsoc_broad2<=53
label variable occsoc_broadgroup "Groups of 2-digit SOC CODES- for HWOL data merge"

egen occyear_broad=group(occsoc_broad2 year)
egen occyear_minor=group(occsoc_minor2 year)
egen occyear_detailed=group(occsoc_detailed2 year)

merge m:1 statefip year using ur0714.dta, keep(master match) nogen assert(2 3)
drop ne_id state
replace ur=ur/100
drop if ur==.

gen totalopenings=1

bys statefip occsoc year: egen avg_exp=mean(exper)
bys statefip occsoc year: egen avg_edu=mean(educ)

collapse (sum) totalopenings expreq exp_0to1 exp_1to2 exp_2to3 exp_3to4 exp_4to5 exp_5to7 exp_7to15 edureq eduhs edusc edubd eduma eduphd (mean) avg_exp avg_edu ur, by(statefip occsoc year)
	

replace occsoc="435111" if occsoc=="43511."
replace occsoc="191029" if occsoc=="191020" // general code for marine biologist combine

drop if substr(occsoc,1,2)=="55"

merge m:1 statefip  year occsoc using merge_variables
tab _merge
keep if _merge==1 | _merge==3
drop _merge


sort statefip year occsoc
egen panelid=group(occsoc statefip)
xtset panelid year

save allyear_tempfile_final.dta, replace


foreach dataset in 2007_2010 2010_2012  {
set varabbrev off
	use allyear_tempfile_final.dta, clear
	xtset panelid year

	if "`dataset'"=="2007_2010" {
		keep if year==2007 | year==2010
		local lag "L3."
	}

	if "`dataset'"=="2010_2012" {
		keep if year==2010 | year==2012
		local lag "L2."
	}

	
gen du =  s_unemprate-`lag's_unemprate

gen BGTsupdemrate=BGTsupdemrate_occsoc_broad	

gen dBGTsupdemrate=[BGTsupdemrate -`lag'BGTsupdemrate]
label variable dBGTsupdemrate "change in LI supdemrate (t+n)-t"

gen dBGTsupdemratebroad=[BGTsupdemrate_occsoc_broad-`lag'BGTsupdemrate_occsoc_broad]
label variable dBGTsupdemratebroad "% change  in BroadOCC LI supdemrate (t+n)-t"

gen lto=`lag'totalopenings
gen minto=min(lto,totalopenings)
gen dto = totalopenings-lto
label variable lto "first period total openings"
label variable minto "minimum total openings: first vs. second period"
label variable dto "difference in total openings: second-first period"

gen coded_edu=(edureq)/totalopenings
gen coded_exp=(expreq)/totalopenings
label variable coded_edu "share of total postings with coded educational qualifications"
label variable coded_exp "share of total postings with coded experience qualifications"

gen mcoded_edu=min(coded_edu, `lag'coded_edu)
gen mcoded_exp=min(coded_exp, `lag'coded_exp)
label variable coded_edu "min. [t,t-n] share of total postings with coded educational qualifications"
label variable coded_exp "min. [t,t-n] share of total postings with coded experience qualifications"


gen ato=(lto+totalopenings)/2
egen state_ato=sum(ato), by (statefip year)
drop ato state_ato
label variable occshare_state_ato "occupation share of state's average total openings"


gen neednoedu=((totalopenings-edureq)/totalopenings)*100
foreach type in hs sc bd ma phd {
gen need`type'=(edu`type'/totalopenings)*100
	}
	
egen needgths=rowtotal(needsc needbd needma needphd)
egen needbd_plus = rowtotal(needbd needma needphd)
egen needgd=rowtotal(needma needphd)	

gen needna=((totalopenings-expreq)/totalopenings)*100
foreach type in 0to1 1to2 2to3 3to4 4to5 5to7 7to15 {
gen need`type'=(exp_`type'/totalopenings)*100
	}
	
egen need2= rowtotal(need2to3 need3to4 need4to5 need5to7 need7to15)
egen need5= rowtotal(need5to7 need7to15)
egen need3= rowtotal(need3to4 need4to5 need5to7 need7to15)
	
label variable needbd "share of job postings requesting BD"
label variable needbd_plus "share of job posting pref. BD+"
label variable need5 "share of jobs postings requiring at least 5 years experience"
label variable need2 "share of jobs postings requiring at least 2 years experience"

foreach var of varlist needbd_plus needgd needgths need2 need5 need3 needhs needbd needma needphd needsc needna neednoedu  {
		gen d`var'=`var'-`lag'`var'
	}

	

gen needba_coded = ((educ_bd + educ_gd)/(totalopenings-educ_unknown))*100
gen needba_pref_coded = ((educ_bd_gd)/(totalopenings-educ_unknown))*100

gen need5_coded= ((exp_5to8 + exp_8plus)/(totalopenings-exp_na))*100
gen need2_coded= ((exp_2to5 + exp_5to8 + exp_8plus)/(totalopenings-exp_na))*100
gen need2to5_coded= ((exp_2to5)/(totalopenings-exp_na))*100

gen dneedba_pref_coded=needba_pref_coded-`lag'needba_pref_coded
gen dneed5_coded=need5_coded-`lag'need5_coded
gen dneed2_coded=need2_coded-`lag'need2_coded
gen dneed2to5_coded=need2to5_coded-`lag'need2to5_coded


	save BGT_`dataset'_withvars.dta, replace
}



use BGT_2010_2012_withvars.dta, clear
keep if year==2012
append using BGT_2007_2010_withvars.dta

preserve
collapse (sum) totalopenings, by(statefip occsoc)
bys statefip: egen total=total(totalopenings)
gen occshare_state=totalopenings/total
label variable occshare_state "Occupation Share of State's Total Openings (WEIGHT)"
tempfile a
save `a', replace
restore

merge m:1 statefip occsoc using `a', keepusing(occshare_state) nogen

sort statefip year occsoc
xtset panelid year

gen initial_openings_per_emp=L3.openings_per_emp if year==2010
replace initial_openings_per_emp=L5.openings_per_emp if year==2012
label variable initial_openings_per_emp "2007 number of postings per employee"
	   
foreach var of varlist neednoedu needhs needsc needbd needma needphd needgths needbd_plus ///
needgd needna need0to1 need1to2 need2to3 need3to4 need4to5 need5to7 need7to15 need2 need5 need3 {
gen initial_`var'07=L3.`var' if year==2010
replace initial_`var'07=L5.`var' if year==2012
}



gen change_openings_per_emp=(F2.totalopenings-L3.totalopenings)/L3.s_occ_emp if year==2010
replace change_openings_per_emp=(totalopenings-L5.totalopenings)/L5.s_occ_emp if year==2012
label variable change_openings_per_emp "Change in Number of Postings per employed person"

gen estsample=0
replace estsample=1 if (year==2010 | year==2012) & change_openings_per_emp!=. & minto>15 & du!=.
label variable estsample "dummy: identifies observations in main specifications (used for change summary stats)"

drop sumsample
gen sumsample=estsample
replace sumsample=1 if F3.estsample==1
label variable sumsample "dummy: identifies observations to be used for level summary statistics"

capture egen styear=group(state year)
capture egen occyear=group(occsoc year)
capture egen occst=group(occsoc state)


saveold BGT_2007_2010_2012_firmjobtitle_collapse_032919.dta, replace version(11)


